USE TMS
GO


/*
IF OBJECT_ID ( 'tb_ConfigType', 'U' ) IS NOT NULL 
    DROP table tb_ConfigType;
GO
CREATE table tb_ConfigType
(
TypeID int,
TypeName nvarchar(10)
)
--*/

------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------

/*
IF OBJECT_ID ( 'tb_Config', 'U' ) IS NOT NULL 
    DROP table tb_Config;
GO
CREATE table tb_Config
(
ID int identity(1,1),
ItemName nvarchar(20),
TypeID int
)
--*/


------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID ( 'tb_ConfigType_GetAll', 'P' ) IS NOT NULL 
    DROP PROCEDURE tb_ConfigType_GetAll;
GO
CREATE PROCEDURE tb_ConfigType_GetAll
AS    
    select TypeID,TypeName
	from tb_ConfigType  
	order by TypeID 
GO

------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------


IF OBJECT_ID ( 'tb_Config_GetAll', 'P' ) IS NOT NULL 
    DROP PROCEDURE tb_Config_GetAll;
GO
CREATE PROCEDURE tb_Config_GetAll
AS    
    select ID,ItemName,c.TypeID,TypeName
	from tb_Config c inner join tb_ConfigType ct on c.TypeID=ct.TypeID
	order by TypeID 
GO


IF OBJECT_ID ( 'tb_Config_GetbyID', 'P' ) IS NOT NULL 
    DROP PROCEDURE tb_Config_GetbyID;
GO
CREATE PROCEDURE tb_Config_GetbyID
    @ID int 
AS    
    select ID,ItemName,c.TypeID,TypeName
	from tb_Config c inner join tb_ConfigType ct on c.TypeID=ct.TypeID
	where ID=@ID  
GO


IF OBJECT_ID ( 'tb_Config_Insert', 'P' ) IS NOT NULL 
    DROP PROCEDURE tb_Config_Insert;
GO
CREATE PROCEDURE tb_Config_Insert
    @ItemName nvarchar(20),
    @TypeID int
AS    
    insert into tb_Config(ItemName,TypeID) 
    select @ItemName,@TypeID
GO


IF OBJECT_ID ( 'tb_Config_Delete', 'P' ) IS NOT NULL 
    DROP PROCEDURE tb_Config_Delete;
GO
CREATE PROCEDURE tb_Config_Delete
    @ID int
AS    
    if(Exists(select * from tb_Config where ID=@ID and TypeID=1))
	   delete from tb_ToolRoom where ID=@ID
    delete from tb_Config where ID=@ID   
GO
















































